package com.dinghui.salary.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.dinghui.salary.mapper.MouthTypeMapper;
import com.dinghui.salary.mapper.SalaryCalculateEquationMapper;
import com.dinghui.salary.mapper.SalaryMapper;
import com.dinghui.salary.mapper.SalaryTypeMapper;
import com.dinghui.salary.pojo.*;
import com.dinghui.salary.pojo.dto.SalaryDTO;
import com.dinghui.salary.pojo.dto.SearchSalaryDTO;
import com.dinghui.salary.pojo.vo.SalaryFieldVO;
import com.dinghui.salary.pojo.vo.SalaryCalculateEquationVO;
import com.dinghui.salary.pojo.vo.SalaryVO;
import com.dinghui.salary.pojo.vo.SelectSalaryTypeVO;
import com.dinghui.salary.service.SalaryService;
import com.dinghui.salary.utils.JDBC;
import com.dinghui.salary.utils.PagedGridResult;
import com.github.pagehelper.PageHelper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import tk.mybatis.mapper.entity.Example;
import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;


/**
 * @author tanrentao
 * @date 2021-09-06
 * @desc
 */
@Slf4j
@Service(version = "2.0.0")
public class SalaryServiceImpl extends BaseService implements SalaryService {

    @Autowired
    private SalaryMapper salaryMapper;

    @Autowired
    private SalaryTypeMapper salaryTypeMapper;

    @Autowired
    private MouthTypeMapper mouthTypeMapper;

    @Autowired
    private SalaryCalculateEquationMapper equatioMapper;

    public int modify(SalaryDTO dto) {
        Example example = new Example(Salary.class);
        example.createCriteria().andEqualTo("id", dto.getId());

        Date date = new Date();

        Salary sa = new Salary();

        sa.setUpdateTime(date);

        if (StringUtils.isNotEmpty(dto.getId())) {
            sa.setId(dto.getId());
        }
        int i = salaryMapper.updateByExampleSelective(sa, example);
        return i;
    }



    @Override
    public int modify(Map maps, Page dto) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        Statement sta = null;

        //查询表名
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        String value = (String) maps.get("yearMonth");
        if (StringUtils.isNotBlank(value)) {
            criteria.andEqualTo("time", value);
        }
        criteria.andEqualTo("isDelete", 0);
        List<MouthType> salaryList = mouthTypeMapper.selectByExample(example);
        if(salaryList.size() != 1){
            return 1;//表不存在
        }
        //查询字段
        List<SelectSalaryTypeVO> list = salaryTypeMapper.selectList(value);
        if(list.size() < 1){
            return 2;//修改失败
        }
        String sql = "UPDATE " + salaryList.get(0).getTableName() + " SET update_by=" +"'"+ dto.getUpdate_by()+"',"+ "update_time=" +"'"+dto.getUpdate_time()+"',";
        int length = list.size();
        for (int t = 0; t < length; t++) {
            if(maps.get(list.get(t).getName()) == null){
                sql+= list.get(t).getName() +"="+ "0";
            }else{
                sql+= list.get(t).getName() +"="+ maps.get(list.get(t).getName());
            }
            if(t < length-1){
                sql+=",";
            }
        }
        sql+=" where id=" +"'"+ maps.get("id")+"'";
        try {
            String db= "dh_salary";
            conn = JDBC.getConnection(db);
//            st = conn.prepareStatement(sql);
//            st.executeQuery();
            sta = conn.createStatement();
            sta.executeUpdate(sql);
            sta = conn.createStatement();
            return 3;
        } catch (SQLException throwables) {
        throwables.printStackTrace();
        } finally {
            JDBC.closeResource(conn,st,rs);
        }
        return 4;
    }

    @Override
    public int equationQuery(Page dto) {
        int i = 0;

        List<Map> list = new ArrayList();
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        String yearMonth = "";
        Statement sta = null;
        if(Integer.parseInt(dto.getMouth()) < 10){
            yearMonth = dto.getYear() + "-" + "0" + dto.getMouth();
        }else{
            yearMonth = dto.getYear() + "-" + dto.getMouth();
        }
        //查询表名
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        if (StringUtils.isNotBlank(yearMonth)) {
            criteria.andEqualTo("time", yearMonth);
        }
        criteria.andEqualTo("isDelete", 0);
        List<MouthType> salaryList = mouthTypeMapper.selectByExample(example);
        if(salaryList.size() != 1){
            return 0;//表不存在
        }
        //查询这个月份的计算公式
        SalaryCalculateEquationVO Eq = equatioMapper.selectOneByTime(yearMonth,Integer.parseInt(dto.getType()));
        if(Eq == null){
            return 1;//没有这个月份的计算公式
        }
        String[] EqList = Eq.getExpression_list().split(",");
        try {
            String db= "dh_salary";
            conn = JDBC.getConnection(db);
            String sql = "SELECT * FROM " + salaryList.get(0).getTableName();
            System.out.println("建表语句是："+sql);
            st = conn.prepareStatement(sql);
            rs = st.executeQuery();
            if (rs == null) {
                return 2;//数据库连接错误
            }
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                Map rowData = new HashMap();
                for (int j = 1; j <= columnCount; j++) {
                    rowData.put(metaData.getColumnName(j), rs.getObject(j));
                }
                list.add(rowData);
            }

            for (Map item: list ) {
                BigDecimal num = new BigDecimal(0);
                BigDecimal num1 = new BigDecimal(0);
                for (String ite:EqList ) {
                    if(ite.equals("+") || ite.equals("-")){
                        if(ite.equals("+")){
                            num = num.add(num1);
                        }else{
                            num = num.subtract(num1);
                        }

                    }else{
                        BigDecimal nn= (BigDecimal) item.get(ite);
                        if(item.get(ite) != null){
                            num1 = (BigDecimal) item.get(ite);
                        }else{
                            BigDecimal num2 = new BigDecimal(0);
                            num1 = num2;
                        }
                    }
                }
                if(Eq.getType() == 1){//1.应发工资  ， 2.实发工资
                    String sqlUp = "UPDATE " + salaryList.get(0).getTableName() + " SET should_pay = " +num+ ", update_by="+dto.getUpdate_by() +", update_time=" + "'" +dto.getUpdate_time()+ "'" +" where id = " +"'" + item.get("id") + "'";
//                    st = conn.prepareStatement(sqlUp);
//                    st.executeQuery();
                      sta = conn.createStatement();
                      sta.executeUpdate(sqlUp);
                }
                if(Eq.getType() == 2){
                    String sqlUp1 = "UPDATE " + salaryList.get(0).getTableName() + " SET real_wages = " +num+ ", update_by="+dto.getUpdate_by()+", update_time="+ "'" +dto.getUpdate_time()+ "'" +" where id = "+"'" + item.get("id") + "'";
/*                    st = conn.prepareStatement(sqlUp1);
                    st.executeQuery();*/
                    sta = conn.createStatement();
                    sta.executeUpdate(sqlUp1);
                }
//                }
            }
            return 3;//成功

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC.closeResource(conn,st,rs);
        }
        return 4;//失败
    }

    @Override
    public PagedGridResult selectSalaryList(SearchSalaryDTO dto, User user) {
         // 1.查询表名
        Example mouthTypeExample = new Example(MouthType.class);
        String time = dto.getTime();
        String tableName = "";
        if (StringUtils.isNotEmpty(time)) {
            mouthTypeExample.createCriteria().andEqualTo("time", time);
            MouthType mouthType = mouthTypeMapper.selectOneByExample(mouthTypeExample);
            if (ObjectUtils.isNotEmpty(mouthType)) {
                tableName = mouthType.getTableName();
            }
        }

        // 2.查询工资表
        if (StringUtils.isNotEmpty(tableName)) {
            PageHelper.startPage(dto.getPage(), dto.getPageSize());
            ArrayList<HashMap> list = salaryMapper.selectSalaryList(tableName, dto.getIdNumber(), dto.getName());
            return setterPagedGrid(list, dto.getPage());
        }
        return setterPagedGrid(new ArrayList<>(), dto.getPage());
    }

    @Override
    public  List<SalaryFieldVO> selectSalaryFiled(SearchSalaryDTO dto) {
        List<SalaryFieldVO> list = mouthTypeMapper.selectSalaryFiled(dto.getTime());
        if (list.size() > 0) {
            List<SalaryFieldVO> newList = new ArrayList<>();

            SalaryFieldVO salaryFieldVO1 = new SalaryFieldVO("id", "id");
            SalaryFieldVO salaryFieldVO2 = new SalaryFieldVO("身份证号", "id_number");
            SalaryFieldVO salaryFieldVO3 = new SalaryFieldVO("姓名", "name");
            SalaryFieldVO salaryFieldVO4 = new SalaryFieldVO("时间", "time");
            SalaryFieldVO salaryFieldVO5 = new SalaryFieldVO("应发工资", "should_pay");
            SalaryFieldVO salaryFieldVO6 = new SalaryFieldVO("实发工资", "real_wages");
            newList.add(salaryFieldVO1);
            newList.add(salaryFieldVO2);
            newList.add(salaryFieldVO3);
            newList.add(salaryFieldVO4);
            newList.add(salaryFieldVO5);
            newList.add(salaryFieldVO6);

            for (SalaryFieldVO fieldVO : list) {
                SalaryFieldVO salaryField = new SalaryFieldVO(fieldVO.getLabel(), fieldVO.getProp());
                newList.add(salaryField);
            }
            return newList;
        }
        return new ArrayList<>();
    }

    public BigDecimal getNum(SalaryVO item,String ite){
        BigDecimal num1 = new BigDecimal(0);
        return num1;
    }

    public int modifyGZ(SalaryVO dto) {
        Example example = new Example(Salary.class);
        example.createCriteria().andEqualTo("id",dto.getId());

        Date date = new Date();

        Salary sa = new Salary();

        sa.setUpdateBy(dto.getUpdate_by());
        sa.setUpdateTime(date);

        if(StringUtils.isNotEmpty(dto.getId())){
            sa.setId(dto.getId());
        }


        if (dto.getShould_pay() != null ||  "".equals(dto.getShould_pay())) {
            sa.setShouldPay(dto.getShould_pay());
        }
        if (dto.getReal_wages() != null ||  "".equals(dto.getReal_wages())) {
            sa.setRealWages(dto.getReal_wages());
        }
        int i = salaryMapper.updateByExampleSelective(sa,example);
        return i;
    }


    @Override
    public int Delete(Map item,Page dto) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        String yearMonth = "";
        Statement sta = null;

        //查询表名
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        String value = (String) item.get("yearMonth");
        if (StringUtils.isNotBlank(value)) {
            criteria.andEqualTo("time", value);
        }
        criteria.andEqualTo("isDelete", 0);
        List<MouthType> salaryList = mouthTypeMapper.selectByExample(example);
        if(salaryList.size() != 1){
            return 1;//表不存在
        }
        String sql = "UPDATE " + salaryList.get(0).getTableName() + " SET update_by=" +"'"+ dto.getUpdate_by()+"',"+ "update_time=" +"'"+dto.getUpdate_time()+"',"+ "is_delete=true" +" where id ="+"'" + item.get("id") + "'";
        try {
            String db= "dh_salary";
            conn = JDBC.getConnection(db);
//            st = conn.prepareStatement(sql);
//            st.executeQuery();
            sta = conn.createStatement();
            sta.executeUpdate(sql);
            sta = conn.createStatement();
            return 2;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC.closeResource(conn,st,rs);
        }
        return 3;
    }

    public PagedGridResult FindAlls(Page dto) {
        List list = new ArrayList();
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        String yearMonth = "";
        if(Integer.parseInt(dto.getMouth()) < 10){
            yearMonth = dto.getYear() + "-" + "0" + dto.getMouth();
        }else{
            yearMonth = dto.getYear() + "-" + dto.getMouth();
        }
        Example example = new Example(MouthType.class);
        Example.Criteria criteria = example.createCriteria();
        if (StringUtils.isNotBlank(yearMonth)) {
            criteria.andEqualTo("time", yearMonth);
        }
        List<MouthType> salaryList = mouthTypeMapper.selectByExample(example);
        if(salaryList.size() < 1){
            return null;
        }
        try {
            String db= "dh_salary";
            conn = JDBC.getConnection(db);
            String sql = "SELECT * FROM " + salaryList.get(0).getTableName() +" where is_delete=0";
            st = conn.prepareStatement(sql);
            rs = st.executeQuery();
            if (rs == null) {
                return null;
            }
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (rs.next()) {
                Map rowData = new HashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(metaData.getColumnName(i), rs.getObject(i));
                }
                list.add(rowData);
            }
            PageHelper.startPage(dto.getPage(), dto.getPageSize());
            return setterPagedGrid(list, dto.getPage());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBC.closeResource(conn,st,rs);
        }
        return null;
    }

    @Override
    public PagedGridResult FindAll(Page dto) {
        String tableName = "";
        String yearMonth = "";
        if(!StringUtils.isNotEmpty(dto.getMouth())){
            return null;
        }
        if(!StringUtils.isNotEmpty(dto.getYear())){
            return null;
        }
        if(Integer.parseInt(dto.getMouth()) < 10){
            yearMonth = dto.getYear() + "-" + "0" + dto.getMouth();
        }else{
            yearMonth = dto.getYear() + "-" + dto.getMouth();
        }
        // 1.查询表名
        Example mouthTypeExample = new Example(MouthType.class);
        if (StringUtils.isNotEmpty(yearMonth)) {
            mouthTypeExample.createCriteria().andEqualTo("time", yearMonth);
            MouthType mouthType = mouthTypeMapper.selectOneByExample(mouthTypeExample);
            if (ObjectUtils.isNotEmpty(mouthType)) {
                tableName = mouthType.getTableName();
            }
        }

        // 2.查询工资表
        if (StringUtils.isNotEmpty(tableName)) {
            PageHelper.startPage(dto.getPage(), dto.getPageSize());
            ArrayList<HashMap> list = salaryMapper.FindAlls(tableName, yearMonth);
            return setterPagedGrid(list, dto.getPage());
        }
        return setterPagedGrid(new ArrayList<>(), dto.getPage());
    }

    @Override
    public Object FindById(Salary dto) {
        return salaryMapper.selectOne(dto);
    }

    public List<SalaryVO> BySalary(List<Salary> list){
        List<SalaryVO> lists = new ArrayList<>();
        for (Salary item:list) {
            SalaryVO salary = new SalaryVO();
            salary.setId(item.getId());
            salary.setName(item.getName());
            salary.setJob_number(item.getJobNumber());
            lists.add(salary);
        }
        return lists;
    }
}
